package com.example.myutil;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;

public class JDBCTools {

	private static DruidDataSource dataSource = null;

	//数据库连接池应只被初始化一次. 
	static{
		dataSource = new DruidDataSource();
	}
	
	public static enum DATABASETYPE {  
        ORACLE, MYSQL, SQLSERVER, SQLSERVER2005, DB2, POSTGRESQL ,HANA, OTHER, EMPTY
    } 
	
	/**
	 * 获取数据库的数据库名
	 * @param url "jdbc:mysql://localhost:3306"
	 * @param ip
	 * @param port
	 * @param username
	 * @param password
	 * @return
	 */
	public static List<Map<String, String>> getDatabaseList(String databasetype, String ip, String port,String username, String password){
		  //String sql="SELECT SCHEMA_NAME FROM information_schema.SCHEMATA";
		//sqlserver url:jdbc:sqlserver://localhost:1433; DatabaseName=ItemManager
		// 去除首尾空格  
        databasetype = trim(databasetype);  
        ip = trim(ip);  
        port = trim(port);  
        username = trim(username);  
        password = trim(password);  
        //  
        DATABASETYPE dbtype = parseDATABASETYPE(databasetype);  
        //  
        List<Map<String, String>> result = new ArrayList<Map<String,String>>();  
        String url = concatDBURL2(dbtype, ip, port);  
        Connection conn = getConnection2(url, username, password);
        ResultSet rs = null;
		try{
	        // Statement stmt = null;  
			DatabaseMetaData dmd=conn.getMetaData();
			if (DATABASETYPE.ORACLE.equals(dbtype)) { 
				
            } else if (DATABASETYPE.MYSQL.equals(dbtype)) {
            	rs=dmd.getCatalogs();
                // Mysql查询  
            	while(rs.next()){
            		Map<String, String> map=new HashMap<>();
            		map.put("id",Long.toString(System.currentTimeMillis()));
            		map.put("databaseName", rs.getString("TABLE_CAT"));
            		map.put("state", "closed");
            		map.put("databaseType", "database");
            		map.put("iconCls","icon-hamburg-database");
            		result.add(map);
    			}
            }  else if (DATABASETYPE.SQLSERVER.equals(dbtype) || DATABASETYPE.SQLSERVER2005.equals(dbtype)) {  
            	String sql="select * from sys.databases";
            	Statement stmt=conn.createStatement();//创建SQL命令对象
            	rs=stmt.executeQuery(sql);//返回SQL语句查询结果集(集合)
            	while(rs.next()){
            		Map<String, String> map=new HashMap<>();
            		map.put("id",rs.getString("name")+Long.toString(System.currentTimeMillis()));
            		map.put("databaseName", rs.getString("name"));
            		map.put("state", "closed");
            		map.put("databaseType", "database");
            		map.put("iconCls","icon-hamburg-database");
            		result.add(map);
    			}
            	
            }  else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) {  
            	String sql="SELECT datname FROM pg_database;";
            	PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql);
	        	rs = pstmt.executeQuery(); 
	        	int col = rs.getMetaData().getColumnCount();
	        	while (rs.next()) {
	                for (int i = 1; i <= col; i++) {  
	                    System.out.print(rs.getString(i) + "\t");
	                    Map<String, String> map=new HashMap<>();
	            		map.put("id",rs.getString(i)+Long.toString(System.currentTimeMillis()));
	            		map.put("databaseName", rs.getString(i));
	            		map.put("state", "closed");
	            		map.put("databaseType", "database");
	            		map.put("iconCls","icon-hamburg-database");
	            		result.add(map);
	                    if ((i == 2) && (rs.getString(i).length() < 8)) {  
	                        System.out.print("\t");  
	                    }  
	                 }  
	            }
            }else if(DATABASETYPE.HANA.equals(dbtype)) {
            	String sql="select NAME FROM SYS.P_SCHEMAS_ order by Name";
            	PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql);
	        	rs = pstmt.executeQuery(); 
	        	int col = rs.getMetaData().getColumnCount();
	        	while (rs.next()) {
	                for (int i = 1; i <= col; i++) {  
	                    System.out.print(rs.getString(i) + "\t");
	                    Map<String, String> map=new HashMap<>();
	            		map.put("id",rs.getString(i)+Long.toString(System.currentTimeMillis()));
	            		map.put("databaseName", rs.getString(i));
	            		map.put("state", "closed");
	            		map.put("databaseType", "database");
	            		map.put("iconCls","icon-hamburg-database");
	            		result.add(map);
	                    if ((i == 2) && (rs.getString(i).length() < 8)) {  
	                        System.out.print("\t");  
	                    }  
	                 }  
	            }
            } else {  
                throw new RuntimeException("不认识的数据库类型!");  
            }
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			releaseDB(rs,null,conn);
		}
		return result;
	}
	
	/** 
     * 列出数据库的所有表 
     */  
    // 可以参考: http://www.cnblogs.com/chinafine/articles/1847205.html  
    public static List<Map<String, Object>> listTables(String databasetype, String ip, String port, String dbname,  
            String username, String password) {  
        // 去除首尾空格  
        databasetype = trim(databasetype);  
        ip = trim(ip);  
        port = trim(port);  
        dbname = trim(dbname);  
        username = trim(username);  
        password = trim(password);  
        //  
        DATABASETYPE dbtype = parseDATABASETYPE(databasetype);  
        //  
        List<Map<String, Object>> result = null;  
        String url = concatDBURL(dbtype, ip, port, dbname);  
        Connection conn = getConnection2(url, username, password);  
        // Statement stmt = null;  
        ResultSet rs = null;  
        //  
        try {  
            // 这句话我也不懂是什么意思... 好像没什么用  
        	if(!DATABASETYPE.HANA.equals(dbtype)) {
        		conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 
        	}
            // 获取Meta信息对象  
            DatabaseMetaData meta = conn.getMetaData();  
            // 数据库  
            String catalog = null;  
            // 数据库的用户  
            String schemaPattern = null;// meta.getUserName();  
            // 表名  
            String tableNamePattern = null;//  
            // types指的是table、view  
            String[] types = { "TABLE" };  
            // Oracle  
            if (DATABASETYPE.ORACLE.equals(dbtype)) {  
                schemaPattern = username;  
                if (null != schemaPattern) {  
                    schemaPattern = schemaPattern.toUpperCase();  
                }  
                // 查询  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else if (DATABASETYPE.MYSQL.equals(dbtype)) {  
                // Mysql查询  
                // MySQL 的 table 这一级别查询不到备注信息  
                schemaPattern = dbname;  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) {  
                // POSTGRESQL查询  
                // POSTGRESQL 的 table 这一级别查询不到备注信息  
                schemaPattern = "%"; 
                tableNamePattern = "%";
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }  else if (DATABASETYPE.SQLSERVER.equals(dbtype) || DATABASETYPE.SQLSERVER2005.equals(dbtype)) {  
                // SqlServer  
                tableNamePattern = "%";  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }  else if (DATABASETYPE.DB2.equals(dbtype)) {  
                // DB2查询  
                schemaPattern = "jence_user";  
                tableNamePattern = "%";  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else if (DATABASETYPE.HANA.equals(dbtype)) {  
                // hana查询  
                schemaPattern = dbname;  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else {  
                throw new RuntimeException("不认识的数据库类型!");  
            }  
            //  
            result = parseResultSetToMapList(rs,databasetype);  
  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
        	releaseDB(rs,null,conn);  
        }  
        //  
        return result;  
    }  
    
    /**
     * 获取数据库中的所有视图
     * @param databasetype
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @return
     */
    public static List<Map<String, Object>> listViews(String databasetype, String ip, String port, String dbname,  
            String username, String password){
    	// 去除首尾空格  
        databasetype = trim(databasetype);  
        ip = trim(ip);  
        port = trim(port);  
        dbname = trim(dbname);  
        username = trim(username);  
        password = trim(password);  
        //  
        DATABASETYPE dbtype = parseDATABASETYPE(databasetype);  
        //  
        List<Map<String, Object>> result = null;  
        String url = concatDBURL(dbtype, ip, port, dbname);  
        Connection conn = getConnection2(url, username, password);  
        // Statement stmt = null;  
        ResultSet rs = null;  
        //  
        try {  
            // 这句话我也不懂是什么意思... 好像没什么用  
        	if(!DATABASETYPE.HANA.equals(dbtype)) {
        		conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 
        	}
            // 获取Meta信息对象  
            DatabaseMetaData meta = conn.getMetaData();  
            // 数据库  
            String catalog = null;  
            // 数据库的用户  
            String schemaPattern = null;// meta.getUserName();  
            // 表名  
            String tableNamePattern = null;//  
            // types指的是table、view  
            String[] types = { "VIEW" };  
            // Oracle  
            if (DATABASETYPE.ORACLE.equals(dbtype)) {  
                schemaPattern = username;  
                if (null != schemaPattern) {  
                    schemaPattern = schemaPattern.toUpperCase();  
                }  
                // 查询  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else if (DATABASETYPE.MYSQL.equals(dbtype)) {  
                // Mysql查询  
                // MySQL 的 table 这一级别查询不到备注信息  
                schemaPattern = dbname;  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) {  
                // POSTGRESQL查询  
                // POSTGRESQL 的 table 这一级别查询不到备注信息  
                schemaPattern = "%"; 
                tableNamePattern = "%";
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }  else if (DATABASETYPE.SQLSERVER.equals(dbtype) || DATABASETYPE.SQLSERVER2005.equals(dbtype)) {  
                // SqlServer  
                tableNamePattern = "%";  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            }  else if (DATABASETYPE.DB2.equals(dbtype)) {  
                // DB2查询  
                schemaPattern = "jence_user";  
                tableNamePattern = "%";  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else if (DATABASETYPE.HANA.equals(dbtype)) {  
                // hana查询  
                schemaPattern = dbname;  
                rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
            } else {  
                throw new RuntimeException("不认识的数据库类型!");  
            }  
            //  
            result = parseResultSetToMapList(rs,databasetype);  
  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
        	releaseDB(rs,null,conn);  
        }  
        //  
        return result;
    }
    
    
    /** 
     * 将一个未处理的ResultSet解析为Map列表. 
     *  
     * @param rs 
     * @return 
     */  
    public static List<Map<String, Object>> parseResultSetToMapList(ResultSet rs,String databasetype) {  
        //  
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();  
        //  
        if (null == rs) {  
            return null;  
        }  
        //  
        try {  
            while (rs.next()) {  
                //   
                Map<String, Object> map = parseResultSetToMap(rs,databasetype);  
                //  
                if (null != map) {  
                    result.add(map);  
                }  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        //  
        return result;  
    }  
    
    /** 
     * 解析ResultSet的单条记录,不进行 ResultSet 的next移动处理 
     *  
     * @param rs 
     * @return 
     */  
    private static Map<String, Object> parseResultSetToMap(ResultSet rs,String databasetype) {  
        //  
        if (null == rs) {  
            return null;  
        }  
        //  
        Map<String, Object> map = new HashMap<String, Object>();  
        //  
        try {  
            ResultSetMetaData meta = rs.getMetaData();  
            //  
            int colNum = meta.getColumnCount();  
            //  
            for (int i = 1; i <= colNum; i++) {
            	DATABASETYPE dbtype = parseDATABASETYPE(databasetype);
            	// 列名  
                String name = meta.getColumnLabel(i); // i+1  
                Object value = rs.getObject(i);
            	if (DATABASETYPE.ORACLE.equals(dbtype)) { 
            		
                } else if (DATABASETYPE.MYSQL.equals(dbtype)) { 
                	if("TABLE_NAME".equals(name)) {
                		name="table_name";
                	}else if("TABLE_TYPE".equals(name)) {
                		name="table_type";
                		//{field:'TABLE_TYPE',title:'表类型',width:160},
                	}else if("REMARKS".equals(name)) {
                		name="remarks";
                	}
		            //{field:'REMARKS',title:'表描述',width:160}
                }else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) { 
                	
                }  else if (DATABASETYPE.SQLSERVER.equals(dbtype) || DATABASETYPE.SQLSERVER2005.equals(dbtype)) { 
                	if("TABLE_NAME".equals(name)) {
                		name="table_name";
                	}else if("TABLE_TYPE".equals(name)) {
                		name="table_type";
                		//{field:'TABLE_TYPE',title:'表类型',width:160},
                	}else if("REMARKS".equals(name)) {
                		name="remarks";
                	}
                } else if (DATABASETYPE.HANA.equals(dbtype)) {  
                	if("TABLE_NAME".equals(name)) {
                		name="table_name";
                	}else if("TABLE_TYPE".equals(name)) {
                		name="table_type";
                		//{field:'TABLE_TYPE',title:'表类型',width:160},
                	}else if("REMARKS".equals(name)) {
                		name="remarks";
                	}
                } else {  
                    throw new RuntimeException("不认识的数据库类型!");  
                }
                // 加入属性  
                map.put(name, value);  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        //  
        return map;  
    }  
    
  
    /** 
     * 列出表的所有字段 
     */  
    public static List<Map<String, Object>> listColumns(String databasetype, String ip, String port, String dbname,  
            String username, String password, String tableName) {  
        // 去除首尾空格  
        databasetype = trim(databasetype);  
        ip = trim(ip);  
        port = trim(port);  
        dbname = trim(dbname);  
        username = trim(username);  
        password = trim(password);  
        tableName = trim(tableName);  
        //  
        DATABASETYPE dbtype = parseDATABASETYPE(databasetype);  
        //  
        List<Map<String, Object>> result = null;  
        String url = concatDBURL(dbtype, ip, port, dbname);  
        Connection conn = getConnection2(url, username, password);  
        // Statement stmt = null;  
        ResultSet rs = null;  
        try {  
            // 获取Meta信息对象  
            DatabaseMetaData meta = conn.getMetaData();  
            // 数据库  
            String catalog = null;  
            // 数据库的用户  
            String schemaPattern = null;// meta.getUserName();  
            // 表名  
            String tableNamePattern = tableName;//  
            // 转换为大写  
            if (null != tableNamePattern && !DATABASETYPE.POSTGRESQL.equals(dbtype)) {  
                tableNamePattern = tableNamePattern.toUpperCase();  
            }  
            //   
            String columnNamePattern = null;  
            // Oracle  
            if (DATABASETYPE.ORACLE.equals(dbtype)) {  
                // 查询  
                schemaPattern = username;  
                if (null != schemaPattern) {  
                    schemaPattern = schemaPattern.toUpperCase();  
                }  
            }else if(DATABASETYPE.POSTGRESQL.equals(dbtype)) {
            	//获取所有列ResultSet columnSet = dbmt.getColumns(null,"%", tableName,"%");
            	schemaPattern="%";
            	columnNamePattern="%";
            }else if (DATABASETYPE.HANA.equals(dbtype)) {
            	schemaPattern=dbname;
			}   
            rs = meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);  
            // TODO 获取主键列,但还没使用  
            meta.getPrimaryKeys(catalog, schemaPattern, tableNamePattern);  
            //  
            result = parseResultSetToMapList(rs,databasetype);  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            // 关闭资源  
        	releaseDB(rs,null,conn);  
        }  
        //  
        return result;  
    } 
	
    /**
     * 获取视图结构
     * @param databasetype
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @param tableName
     * @return
     */
    public static List<Map<String, Object>> viewStructure(String databasetype, String ip, String port, String dbname,  
            String username, String password, String viewName) {
    	// 去除首尾空格  
        databasetype = trim(databasetype);  
        ip = trim(ip);  
        port = trim(port);  
        dbname = trim(dbname);  
        username = trim(username);  
        password = trim(password);  
        viewName = trim(viewName);  
        //  
        DATABASETYPE dbtype = parseDATABASETYPE(databasetype);  
        //  
        List<Map<String, Object>> result = null;  
        String url = concatDBURL(dbtype, ip, port, dbname);  
        Connection conn = getConnection2(url, username, password);  
        PreparedStatement pStmt=null;
        ResultSet rs = null;
        String sql="";
        try {  
            // 获取Meta信息对象  
            DatabaseMetaData meta = conn.getMetaData();  
            // 数据库  
            String catalog = null;  
            // 数据库的用户  
            String schemaPattern = null;// meta.getUserName();  
            // 表名  
            String tableNamePattern = viewName;//  
            String columnNamePattern = null;  
            // Oracle  
            if (DATABASETYPE.SQLSERVER.equals(dbtype)) {  
                //sql="select * from "+dbname+".INFORMATION_SCHEMA.VIEWS where TABLE_NAME=?";
            	sql="select TABLE_CATALOG table_catalog,TABLE_SCHEMA table_schema,"
            			+ "VIEW_DEFINITION view_definition,table_name,TABLE_CATALOG table_catalog "
            			+ "from "+dbname+".information_schema.VIEWS where table_name=?";
                pStmt = conn.prepareStatement(sql);
            	pStmt.setString(1, viewName);
    	        rs = pStmt.executeQuery();
            }else if(DATABASETYPE.MYSQL.equals(dbtype)) {
            	sql="select TABLE_CATALOG table_catalog,TABLE_SCHEMA table_schema,"
            			+ "VIEW_DEFINITION view_definition,table_name,TABLE_CATALOG table_catalog "
            			+ "from information_schema.VIEWS where table_name=?";
            	pStmt = conn.prepareStatement(sql);
            	pStmt.setString(1, viewName);
    	        rs = pStmt.executeQuery();
            }else if(DATABASETYPE.POSTGRESQL.equals(dbtype)) {
            	//sql="select * from information_schema.VIEWS where table_name=?";
            	sql="select TABLE_CATALOG table_catalog,TABLE_SCHEMA table_schema,"
            			+ "VIEW_DEFINITION view_definition,table_name,TABLE_CATALOG table_catalog "
            			+ "from information_schema.VIEWS where table_name=?";
            	pStmt = conn.prepareStatement(sql);
            	pStmt.setString(1, viewName);
    	        rs = pStmt.executeQuery();
            }else if (DATABASETYPE.HANA.equals(dbtype)) {
            	//sql="select * table_schema from "+dbname+".INFORMATION_SCHEMA.VIEWS where table_name=?";
            	sql="select TABLE_CATALOG table_catalog,TABLE_SCHEMA table_schema,"
            			+ "VIEW_DEFINITION view_definition,table_name,TABLE_CATALOG table_catalog "
            			+ "from "+dbname+".information_schema.VIEWS where table_name=?";
            	pStmt = conn.prepareStatement(sql);
            	pStmt.setString(1, viewName);
    	        rs = pStmt.executeQuery();
			}   
            //rs = meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);  
            // TODO 获取主键列,但还没使用  
            //meta.getPrimaryKeys(catalog, schemaPattern, tableNamePattern);  
            //  
            result = parseResultSetToMapList(rs,databasetype);  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            // 关闭资源  
        	releaseDB(rs,null,conn);  
        }  
        //  
        return result;
	}
    
    
	/** 
     * 根据字符串,判断数据库类型 
     *  
     * @param databasetype 
     * @return 
     */  
    public static DATABASETYPE parseDATABASETYPE(String databasetype) {  
        // 空类型  
        if (null == databasetype || databasetype.trim().length() < 1) {  
            return DATABASETYPE.EMPTY;  
        }  
        // 截断首尾空格,转换为大写  
        databasetype = databasetype.trim().toUpperCase();  
        // Oracle数据库  
        if (databasetype.contains("ORACLE")) {  
            //  
            return DATABASETYPE.ORACLE;  
        }  
        // MYSQL 数据库  
        if (databasetype.contains("MYSQL")) {  
            //  
            return DATABASETYPE.MYSQL;  
        }
        if (databasetype.contains("POSTGRESQL")) {  
            //  
            return DATABASETYPE.POSTGRESQL;  
        } 
        // SQL SERVER 数据库  
        if (databasetype.contains("SQL") && databasetype.contains("SERVER")) {  
            //  
            if (databasetype.contains("2005") || databasetype.contains("2008") || databasetype.contains("2012")) {  
  
                try {  
                    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
                } catch (ClassNotFoundException e) {  
                    e.printStackTrace();  
                }  
                  
                return DATABASETYPE.SQLSERVER2005;  
            } else {  
                try {  
                    // 注册 JTDS  com.microsoft.sqlserver.jdbc.SQLServerDriver
                    //Class.forName("net.sourceforge.jtds.jdbc.Driver"); 
                	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                	
                } catch (ClassNotFoundException e) {  
                    e.printStackTrace();  
                }  
                return DATABASETYPE.SQLSERVER;  
            }  
        } 
        if (databasetype.contains("HANA")) {  
            //HANA数据库
        	try {  
            	Class.forName("com.sap.db.jdbc.Driver");
            } catch (ClassNotFoundException e) {  
                e.printStackTrace();  
            }
            return DATABASETYPE.HANA;  
        }
        // 下面的这几个没有经过实践测试, 判断可能不准确  
        // DB2 数据库  
        if (databasetype.contains("DB2")) {  
            //  
            return DATABASETYPE.DB2;  
        }  
        // 默认,返回其他  
        return DATABASETYPE.OTHER;  
    }
    
    /** 
     * 根据IP,端口,以及数据库名字,拼接Oracle连接字符串 
     *  
     * @param ip 
     * @param port 
     * @param dbname 
     * @return 
     */  
    public static String concatDBURL2(DATABASETYPE dbtype, String ip, String port) {  
        //  
        String url = "";  
        // Oracle数据库  
        if (DATABASETYPE.ORACLE.equals(dbtype)) {  
            //  
            url += "jdbc:oracle:thin:@";  
            url += ip.trim();  
            url += ":" + port.trim();  
              
            // 如果需要采用 hotbackup  
            String url2 = "";  
            url2 = url2+"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "  
                    + ip.trim() +")(PORT ="+ port.trim() +")))(CONNECT_DATA = (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))";  
            //  
            // url = url2;  
        } else if (DATABASETYPE.MYSQL.equals(dbtype)) {  
            //  
            url += "jdbc:mysql://";  
            url += ip.trim();  
            url += ":" + port.trim();
        }else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) {//jdbc:postgresql://localhost:5432/testdb  
            //  
            url += "jdbc:postgresql://";  
            url += ip.trim();  
            url += ":" + port.trim();
        } else if (DATABASETYPE.SQLSERVER.equals(dbtype)) {  
            //  "jdbc:sqlserver://localhost:1433"
            url += "jdbc:sqlserver://";  
            url += ip.trim();  
            url += ":" + port.trim(); 
            url += ";tds=8.0;lastupdatecount=true";  
        } else if (DATABASETYPE.SQLSERVER2005.equals(dbtype)) {  
            //  
            url += "jdbc:sqlserver://";  
            url += ip.trim();  
            url += ":" + port.trim(); 
        } else if (DATABASETYPE.DB2.equals(dbtype)) {  
            url += "jdbc:db2://";  
            url += ip.trim();  
            url += ":" + port.trim(); 
        }else if (DATABASETYPE.HANA.equals(dbtype)) {  
            url += "jdbc:sap://";  
            url += ip.trim();  
            url += ":" + port.trim();
            url +="?reconnect=true";
        }else {  
            throw new RuntimeException("不认识的数据库类型!");  
        }  
        //  
        return url;  
    }
    
	
	/** 
     * 根据IP,端口,以及数据库名字,拼接Oracle连接字符串 
     *  
     * @param ip 
     * @param port 
     * @param dbname 
     * @return 
     */  
    public static String concatDBURL(DATABASETYPE dbtype, String ip, String port, String dbname) {  
        //  
        String url = "";  
        // Oracle数据库  
        if (DATABASETYPE.ORACLE.equals(dbtype)) {  
            //  
            url += "jdbc:oracle:thin:@";  
            url += ip.trim();  
            url += ":" + port.trim();  
            url += ":" + dbname;  
              
            // 如果需要采用 hotbackup  
            String url2 = "";  
            url2 = url2+"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "  
                    + ip.trim() +")(PORT ="+ port.trim() +")))(CONNECT_DATA = (SERVICE_NAME ="+dbname+  
                    ")(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))";  
            //  
            // url = url2;  
        } else if (DATABASETYPE.MYSQL.equals(dbtype)) {  
            //  
            url += "jdbc:mysql://";  
            url += ip.trim();  
            url += ":" + port.trim();  
            url += "/" + dbname;
            url += "?characterEncoding=utf8&useSSL=false";
        }else if (DATABASETYPE.POSTGRESQL.equals(dbtype)) {//jdbc:postgresql://localhost:5432/testdb  
            //  
            url += "jdbc:postgresql://";  
            url += ip.trim();  
            url += ":" + port.trim();
            url += "/" + dbname;
            url += "?characterEncoding=utf8&useSSL=false";
        }else if (DATABASETYPE.SQLSERVER.equals(dbtype)) {  
//            url += "jdbc:sqlserver://";  
//            url += ip.trim();  
//            url += ":" + port.trim();  
//            url += "/" + dbname;  
//            url += ";tds=8.0;lastupdatecount=true"; 
        	url += "jdbc:sqlserver://";  
            url += ip.trim();  
            url += ":" + port.trim();  
            url += "; DatabaseName=" + dbname;
        } else if (DATABASETYPE.SQLSERVER2005.equals(dbtype)) {  
            //  
            url += "jdbc:sqlserver://";  
            url += ip.trim();  
            url += ":" + port.trim();  
            url += "; DatabaseName=" + dbname;
        } else if (DATABASETYPE.DB2.equals(dbtype)) {  
            url += "jdbc:db2://";  
            url += ip.trim();  
            url += ":" + port.trim();  
            url += "/" + dbname;  
        }else if (DATABASETYPE.HANA.equals(dbtype)) {  
            url += "jdbc:sap://";  
            url += ip.trim();  
            url += ":" + port.trim();
            url +="?reconnect=true";
        }else {  
            throw new RuntimeException("不认识的数据库类型!");  
        }  
        //  
        return url;  
    }  
	
	
	
	public static Connection getConnection(String driverClass,String jdbcUrl,String username,String password) throws Exception {
		Connection conn=null;
		try {
			dataSource.setDriverClassName(driverClass);
			dataSource.setUrl(jdbcUrl);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
			//3. 指定数据源的一些可选的属性.
			//1). 指定数据库连接池中初始化连接数的个数
			dataSource.setInitialSize(5);
			
			//2). 指定最大的连接数: 同一时刻可以同时向数据库申请的连接数
			dataSource.setMaxActive(5);
			
			//3). 指定小连接数: 在数据库连接池中保存的最少的空闲连接的数量 
			//dataSource.setMinIdle(2);
			dataSource.setMinIdle(5);
			
			//4).等待数据库连接池分配连接的最长时间. 单位为毫秒. 超出该时间将抛出异常. 
			dataSource.setMaxWait(1000 * 5);
			
			conn = dataSource.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	
	/** 
     * 获取JDBC连接 
     *  
     * @param url 
     * @param username 
     * @param password 
     * @return 
     */  
    public static Connection getConnection2(String url, String username, String password) {  
        Connection conn = null;  
        try {  
            // 不需要加载Driver. Servlet 2.4规范开始容器会自动载入  
            // conn = DriverManager.getConnection(url, username, password);  
            //  
            Properties info =new Properties();  
            //  
            info.put("user", username);  
            info.put("password", password);  
            // !!! Oracle 如果想要获取元数据 REMARKS 信息,需要加此参数  
            info.put("remarksReporting","true");  
            // !!! MySQL 标志位, 获取TABLE元数据 REMARKS 信息  
            info.put("useInformationSchema","true");  
            // 不知道SQLServer需不需要设置...  
            //  
            conn = DriverManager.getConnection(url, info);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return conn;  
    }
	
	//处理数据库事务的
	//提交事务
	public static void commit(Connection connection){
		if(connection != null){
			try {
				connection.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//回滚事务
	public static void rollback(Connection connection){
		if(connection != null){
			try {
				connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//开始事务
	public static void beginTx(Connection connection){
		if(connection != null){
			try {
				connection.setAutoCommit(false);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void releaseDB(ResultSet resultSet, Statement statement,
			Connection connection) {

		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	 public static String trim(String str){  
	        if(null != str){  
	            str = str.trim();  
	        }  
	        return str;  
	    } 
}
